﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using Model;
using System.Reflection;
using System.Text;

/// <summary>
///Access 的摘要说明
/// </summary>
public class Access
{
    #region userinfo
    public DataSet Veiry(string uid, string pwd)
    {
        string sql = "SELECT u.*,m.Name FROM dbo.UserInfo u LEFT JOIN dbo.Merchants m ON u.mid=m.ID where uid=@uid and pwd=@pwd";
        SqlParameter[] paras = new SqlParameter[]{
        new SqlParameter("@uid",uid),
        new SqlParameter("@pwd",pwd)
        };
        DataSet ds = SqlHelp.GetDataSet(sql, paras);
        return ds;
    }

    public void UpdateUserInfo(UserInfo user)
    {
        string sql = "update userinfo set username=@username,uid=@uid,pwd=@pwd,sex=@sex,createtime=@createtime,updatetime=@updatetime,skin=@skin,mid=@mid where id=@id";
        SqlParameter[] para = new SqlParameter[] { 
        new SqlParameter("@id",user.ID),
        new SqlParameter("@UserName",user.UserName),
        new SqlParameter("@uid",user.UID),
        new SqlParameter("@pwd",user.PWD),
        new SqlParameter("@sex",user.Sex),
        new SqlParameter("@CreateTime",user.CreateTime),
        new SqlParameter("@Updatetime",user.UpdateTime),
        new SqlParameter("@skin",user.Skin),
        new SqlParameter("@mid",user.MID)
        };
        SqlHelp.Excute(sql, para);
    }

    public DataSet GetAllUser(string sort,int pageSize,int pageNumber,string condition)
    {
        string sql = "select count(*) from userInfo where id<>21 "+
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT u.*,m.Name FROM dbo.UserInfo u LEFT JOIN dbo.Merchants m ON u.mid=m.ID where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllStoreName(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from StoreName where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select * from StoreName where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllProjects(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Project where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select * from Project where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllStaff(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Staff s where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT s.ID,s.Name,CASE Sex WHEN 1 THEN '男' ELSE '女' END SEX,s.Phone,N.Name SNAME FROM dbo.Staff s LEFT JOIN dbo.StoreName n ON s.SID=n.ID where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllSubscribe(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "SELECT COUNT(*) FROM dbo.Subscribe su LEFT JOIN dbo.Staff s ON su.SID=s.ID   LEFT JOIN dbo.StoreName sn ON su.snid=sn.ID   where "+condition+" " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT su.ID,su.Name cname,su.Phone cphone,ISNULL(s.Name,'随机') sname,ISNULL(s.Phone,0) sphone,Time,PerCount,sn.Name snname,CASE ISNULL(su.States,0) WHEN 0 THEN '未操作' WHEN 1 THEN '已确定' ELSE '已取消' END States FROM dbo.Subscribe su LEFT JOIN dbo.Staff s ON su.SID=s.ID   LEFT JOIN dbo.StoreName sn ON su.snid=sn.ID   where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllCard(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from membershipcard " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select dbo.MembershipCard.ID,Type,Title,CreateDate,CASE type WHEN 1 THEN '时间' WHEN 2 THEN '一次性' ELSE '会员卡说明' END type2,s.Name from membershipcard LEFT JOIN dbo.StoreName s ON dbo.MembershipCard.SID=s.ID where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllCardUser(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*)  from carduser c LEFT JOIN dbo.Merchants m ON c.ToUserName=m.ToUserName where " + condition + "" +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select c.*,CASE ISNULL(ActivityStates,0) WHEN 0 THEN '否' ELSE '是' END Act,(SELECT COUNT(*) FROM dbo.Activity WHERE CID=c.id) y,(SELECT COUNT(*) FROM dbo.Activity WHERE CID=c.id AND States=2)t,(SELECT COUNT(*) FROM dbo.BrowseLog WHERE CID=c.id ) b  from carduser c LEFT JOIN dbo.Merchants m ON c.ToUserName=m.ToUserName where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllBuy(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from GroupBuying " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,Title,OldPrice,NewPrice,CASE Status WHEN 1 THEN '启用' ELSE '停用' END Status from GroupBuying where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllMerchants(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Merchants  WHERE id<>6  " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,Name,ToUserName,(SELECT COUNT(*) FROM dbo.DXLog WHERE MID=CONVERT(varchar(50), dbo.Merchants.ID) OR MID=dbo.Merchants.ToUserName) t  from Merchants where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllBuyUser(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from GroupBuyingUser " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select u.ID,g.Title,Name,Phone,FromUserName,CreateDate,CASE u.States WHEN 1 THEN '未使用' ELSE '已使用' END Status,num from GroupBuyingUser u LEFT JOIN dbo.GroupBuying g ON u.GID=g.ID where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllActivityUser(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Activity " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT *,CASE States WHEN 1 THEN '未使用' ELSE '已使用' END St FROM dbo.Activity where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetCardToUse(string sort, int pageSize, int pageNumber, string condition,string con)
    {
        string sql = "SELECT count(*) FROM dbo.Merchants s INNER JOIN  dbo.CardUser c ON s.ToUserName=c.ToUserName AND " + con + "  LEFT JOIN dbo.CardToUse u ON c.ID=u.CID AND " + condition + " LEFT JOIN dbo.MembershipCard m ON u.MID=m.ID  " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT c.*,CASE WHEN  u.ID IS  NULL THEN '未使用' ELSE '已使用' END Status FROM dbo.Merchants s INNER JOIN  dbo.CardUser c ON s.ToUserName=c.ToUserName AND "+con+"  LEFT JOIN dbo.CardToUse u ON c.ID=u.CID AND "+condition+" LEFT JOIN dbo.MembershipCard m ON u.MID=m.ID   ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllInfo(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from information " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,Title,KeyWords,CASE replyType WHEN 1 THEN '关键字回复' WHEN 2 THEN '被关注回复' when 4 then '自定义菜单回复' ELSE '自动回复' END  hh,CASE MsgType WHEN 'news' THEN '图文' WHEN 'music' THEN '音乐' ELSE '文本' END  xx from information where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public void AddUser(UserInfo user)
    {
        string sql = "insert into userinfo values(@username,@uid,@pwd,@sex,@createTime,@updateTime,@skin,@mid)";
        SqlParameter[] para = new SqlParameter[]{
        new SqlParameter("@username",user.UserName),
        new SqlParameter("@uid",user.UID),
        new SqlParameter("@pwd",user.PWD),
        new SqlParameter("@sex",user.Sex),
        new SqlParameter("@createTime",user.CreateTime),
        new SqlParameter("@updateTime",user.UpdateTime),
        new SqlParameter("@skin",user.Skin),
        new SqlParameter("@mid",user.MID)
        };
        SqlHelp.Excute(sql, para);
    }

    public UserInfo GetUserByUID(string UID)
    {
        UserInfo user = null;
        string sql = "select * from userinfo where uid=@uid";
        SqlParameter[] para = new SqlParameter[]{
        new SqlParameter("@uid",UID)
        };
        DataRowCollection drC = SqlHelp.GetDataSet(sql, para).Tables[0].Rows;
        if (drC.Count > 0)
        {
            DataRow dr = drC[0];
            user = new UserInfo();
            user.ID = Convert.ToInt32(dr["id"]);
            user.UserName = dr["userName"].ToString();
            user.UID = dr["uid"].ToString();
            user.PWD = dr["pwd"].ToString();
            user.Sex = dr["sex"].ToString();
            user.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
            user.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]);
            user.Skin = dr["skin"].ToString();
            user.MID = int.Parse(dr["mid"].ToString());
        }
        return user;
    }

    public UserInfo GetUserByID(string ID)
    {
        UserInfo user = null;
        string sql = "select * from userinfo where id=@id";
        SqlParameter[] para = new SqlParameter[]{
        new SqlParameter("@id",ID)
        };
        DataRowCollection drC = SqlHelp.GetDataSet(sql, para).Tables[0].Rows;
        if (drC.Count > 0)
        {
            DataRow dr = drC[0];
            user = new UserInfo();
            user.ID = Convert.ToInt32(dr["id"]);
            user.UserName = dr["userName"].ToString();
            user.UID = dr["uid"].ToString();
            user.PWD = dr["pwd"].ToString();
            user.Sex = dr["sex"].ToString();
            user.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
            user.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]);
            user.Skin = dr["skin"].ToString();
            user.MID = int.Parse(dr["mid"].ToString());
        }
        return user;
    }

    public void DelteUserByID(string ID)
    {
        string sql = "delete from userinfo where id=@id";
        SqlParameter[] para=new SqlParameter[]{
        new SqlParameter("@id",ID)
        };
        SqlHelp.Excute(sql, para);
    }

    public DataSet SelectUserByUID(string uid)
    {
        string sql = "select * from userinfo where uid=@uid";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@uid",uid)
        };
        return SqlHelp.GetDataSet(sql, paras);
    }
    #endregion

    #region menu
    public DataSet GetMenuEntityById(string id)
    {
        string sql = "select * from menu where id=" + id;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetWeiMenuEntityById(string id)
    {
        string sql = "select * from weimenu where id=" + id;
        return SqlHelp.GetDataSet(sql, null);
    }

    public void UpdateMenu(Model.Menu menu)
    {
        string sql = "update menu set MenuName=@MenuName,MenuIcon=@MenuIcon,MenuUrl=@MenuUrl,Suqence=@Suqence,CreateTime=@CreateTime,UpdateTime=@UpdateTime,ParentID=@ParentID where id=@id";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@id",menu.ID),
        new SqlParameter("@MenuName",menu.MenuName),
        new SqlParameter("@MenuIcon",menu.MenuIcon),
        new SqlParameter("@MenuUrl",menu.MenuUrl),
        new SqlParameter("@Suqence",menu.Suqence),
        new SqlParameter("@CreateTime",menu.CreateTime),
        new SqlParameter("@UpdateTime",menu.UpdateTime),
        new SqlParameter("@ParentID",menu.ParentID)
        };
        SqlHelp.Excute(sql, paras);
    }
    public void AddMenu(Model.Menu menu)
    {
        string sql = "insert into menu values( @MenuName,@MenuIcon,@MenuUrl,@Suqence,@CreateTime,@UpdateTime,@ParentID)";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@MenuName",menu.MenuName),
        new SqlParameter("@MenuIcon",menu.MenuIcon),
        new SqlParameter("@MenuUrl",menu.MenuUrl),
        new SqlParameter("@Suqence",menu.Suqence),
        new SqlParameter("@CreateTime",menu.CreateTime),
        new SqlParameter("@UpdateTime",menu.UpdateTime),
        new SqlParameter("@ParentID",menu.ParentID)
        };
        SqlHelp.Excute(sql, paras);
    }
    public void DeleteMenu(string id)
    {
        string sql = "delete from  menu where id=@id ";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@id",id)
        };
        SqlHelp.Excute(sql, paras);
    }

    public void DeleteWeiMenu(string id)
    {
        string sql = "delete from  weimenu where id=@id ";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@id",id)
        };
        SqlHelp.Excute(sql, paras);
    }
    #endregion

    public DataSet GetMenuData(string parentid,string user)
    {
        
        string sql = @"select *,case
        when exists(select 1 from menu where ParentID=a.id) then 'haveC' 
        else 'noC'
        end as haveC
          from menu as a where  parentid=" + parentid;
        if (user != "admin")
            sql += " AND a.ID NOT IN (2,5,35)";
        sql += "  ORDER BY a.Suqence";
        DataSet ds = SqlHelp.GetDataSet(sql, null);
        return ds;
    }

    public DataSet GetWeiMenuData(string parentid, int mid)
    {

        string sql = @"select *,'icon-tip' MenuIcon,case
        when exists(select 1 from weimenu where ParentID=a.id) then 'haveC' 
        else 'noC'
        end as haveC
          from weimenu as a where  parentid=" + parentid+" and mid="+mid;
        DataSet ds = SqlHelp.GetDataSet(sql, null);
        return ds;
    }

    public DataSet GetAllHelpMessage(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Helpmessage where "+condition+" " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT id ID,Time,Title,JJ FROM Helpmessage where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataTable GetWeiMenuByMid(int mid)
    {
        string sql = @"SELECT * FROM (
        select *,case when exists(select 1 from weimenu where ParentID=a.id) then 'haveC' else 'noC' end as haveC
                        from weimenu as a where  parentid=0 
                        UNION ALL
                        SELECT *,CASE when exists(select 1 from weimenu where ParentID=a.id) then 'haveC' else 'noC' end as haveC 
                        FROM  dbo.weimenu a WHERE ParentID IN (SELECT id FROM dbo.weimenu WHERE ParentID=0)
                        )t WHERE t.MID=" + mid+" ORDER BY t.ParentID,t.Suqence";
        return SqlHelp.GetDataSet(sql, null).Tables[0];
    }

    public DataTable GetNewMenu(string uid)
    {
        string sql = @"select *,case when exists(select 1 from menu where ParentID=a.id) then 'haveC' else 'noC' end as haveC
                        from menu as a where  parentid=1
                        UNION ALL
                        SELECT *,CASE when exists(select 1 from menu where ParentID=a.id) then 'haveC' else 'noC' end as haveC 
                        FROM  dbo.Menu a WHERE ParentID IN (SELECT id FROM dbo.Menu WHERE ParentID=1)
                        UNION ALL
                        SELECT *,case when exists(select 1 from menu where ParentID=a.id) then 'haveC' else 'noC' end as haveC 
                        FROM  dbo.Menu a WHERE ParentID IN (SELECT id FROM dbo.Menu WHERE ParentID IN(SELECT id FROM dbo.Menu WHERE ParentID=1))
                        ORDER BY a.ParentID,a.Suqence";
        return SqlHelp.GetDataSet(sql, null).Tables[0];
    }
}